import os
import tkinter as tk
from decimal import Decimal
from tkinter import ttk
import sqlite3
from tkinter import messagebox
from tkcalendar import Calendar

from openpyxl import Workbook
import itertools

from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.worksheet.worksheet import Worksheet


class GOODS:
    ID = 0
    name = 1
    type = 2
    data_date = 3
    make_type = 4
    jiedai = 5
    guige = 6
    num = 7
    price = 8
    amount = 9

    @staticmethod
    def translate_to_name(row):
        record = list(row)
        # 借贷标志 1借 2贷 3结存
        record[GOODS.jiedai] = "借" if row[GOODS.jiedai] == 1 else (
            "贷" if row[GOODS.jiedai] == 2 else ("结存" if row[GOODS.jiedai] == 3 else "未知类型"))
        # 金额分转元
        record[GOODS.price] = record[GOODS.price] / 100
        record[GOODS.amount] = record[GOODS.amount] / 100

        return tuple(record)

    @staticmethod
    def translate_to_code(row):
        record = list(row)
        diff_index = GOODS.amount + 1 - len(row)
        # 金额元转分
        record[GOODS.price - diff_index] = record[GOODS.price - diff_index] * 100
        record[GOODS.amount - diff_index] = record[GOODS.amount - diff_index] * 100

        return tuple(record)


class DataExport:
    @staticmethod
    def export_goods(goods_data):
        def set_sheet_style(sheet: Worksheet):
            # 设置单元格的格式
            font = Font(name='黑体', size=20, bold=True)
            font2 = Font(name='黑体', size=12, bold=False)
            font3 = Font(name='黑体', size=12, bold=False, color="FF0000")
            alignment = Alignment(horizontal='center', vertical='center')
            fill = PatternFill(start_color='FFFFFF', end_color='FFFFFF', fill_type='solid')

            # 创建一个边框对象
            border = Border(left=Side(border_style="medium", color="000000", ),
                            right=Side(border_style="medium", color="000000"),
                            top=Side(border_style="medium", color="000000"),
                            bottom=Side(border_style="medium", color="000000"))

            sheet['A1'].font = font
            sheet['A1'].alignment = alignment
            sheet['A1'].fill = fill
            sheet['A1'].border = border
            sheet['A2'].font = font
            sheet['A2'].alignment = alignment
            sheet['A2'].fill = fill
            sheet['A2'].border = border
            # 时间列稍微宽一点
            sheet.column_dimensions['A'].width = 25

            for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=0, max_col=sheet.max_column):
                for cell in row:
                    cell.fill = fill
                    cell.font = font2
                    cell.alignment = alignment
                    cell.border = border
            # 结存 字体红色
            sheet['J3'].font = font3

        # 创建一个新的工作簿
        wb = Workbook()

        for key, group in itertools.groupby(goods_data, key=lambda x: x[1]):
            # grouped_data.append(list(group))
            data_items = list(group)
            sheet: Worksheet = wb.create_sheet(key)

            # 物品名称
            sheet.append(tuple((data_items[0][GOODS.name], "")))
            # 其他
            sheet.append(tuple((data_items[0][GOODS.type], "")))
            sheet.merge_cells("A1:L1")
            sheet.merge_cells("A2:L2")

            sheet.append(("时间", "摘要", " ", "借方", "", "", "贷方", "", "", "结存", "", ""))
            sheet.append(
                ("时间", "摘要", "规格", "数量", "单价", "金额", "数量", "单价", "金额", "数量", "单价", "金额"))
            sheet.merge_cells("A3:A4")
            sheet.merge_cells("B3:B4")
            sheet.merge_cells("D3:F3")
            sheet.merge_cells("G3:I3")
            sheet.merge_cells("J3:L3")

            for data_item in data_items:
                jiedai = data_item[GOODS.jiedai]
                data_item = GOODS.translate_to_name(data_item)
                # 借贷标志 1借 2贷 3结存
                if jiedai == 1:
                    sheet.append((data_item[GOODS.data_date], data_item[GOODS.make_type], data_item[GOODS.guige],
                                  data_item[GOODS.num], data_item[GOODS.price], data_item[GOODS.amount]))
                elif jiedai == 2:
                    sheet.append((data_item[GOODS.data_date], data_item[GOODS.make_type], data_item[GOODS.guige],
                                  "", "", "",
                                  data_item[GOODS.num], data_item[GOODS.price], data_item[GOODS.amount]))
                elif jiedai == 3:
                    sheet.append((data_item[GOODS.data_date], data_item[GOODS.make_type], data_item[GOODS.guige],
                                  "", "", "", "", "", "",
                                  data_item[GOODS.num], data_item[GOODS.price], data_item[GOODS.amount]))
            # 设置表格样式
            set_sheet_style(sheet)

        # 移除默认工作表
        wb.remove(wb.active)
        # 保存工作簿
        wb.save('goods_detail.xlsx')


class DataStore:
    def __init__(self):
        data_path = "data"
        if not os.path.exists(data_path):
            os.mkdir(data_path)
        # 连接到SQLite数据库
        self.conn = sqlite3.connect(data_path + "\\data.db")

    def close(self):
        # 关闭数据库连接
        self.conn.close()

    def fetch_data(self, data_date):
        """
        查询物品明细
        :return: 物品明细列表
        """
        # 从数据库中获取数据
        cursor = self.conn.cursor()
        try:
            cursor.execute("""
                SELECT id,name,type,data_date,make_type,jiedai,guige,num,price,amount
                FROM goods_detail
                where data_date = "{}"
                order by name asc, data_date asc
            """.format(data_date))
            data = cursor.fetchall()
        finally:
            cursor.close()
        return data

    def fetch_all_data(self):
        """
        查询物品明细
        :return: 物品明细列表
        """
        # 从数据库中获取数据
        cursor = self.conn.cursor()
        try:
            cursor.execute("""
                SELECT id,name,type,data_date,make_type,jiedai,guige,num,price,amount
                FROM goods_detail
                order by name asc, data_date asc
            """)
            data = cursor.fetchall()
        finally:
            cursor.close()
        return data

    def save_goods_detail(self, goods_detail):
        # 将数据插入数据库
        try:
            sql = "INSERT INTO goods_detail (name, type, data_date, make_type, jiedai, guige, num, price, " \
                  "amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "
            self.conn.execute(sql, goods_detail)
            return True
        except:
            return False

    def del_good(self, id):
        self.conn.execute("delete from goods_detail where id={}".format(id))
        self.conn.commit()


class DataManage:
    def __init__(self):
        self.dataStore: DataStore = None
        self.root: tk.Tk = None
        self.table: ttk.Treeview = None
        self.is_open_add_form: False
        self.sel_date: None
        self.is_open_add_form = False

    def run(self):
        self.dataStore = DataStore()

        # 创建主窗口
        root = tk.Tk()
        root.title("物品管理系统")
        self.root = root

        top_frame = tk.Frame(root)
        top_frame.pack(side="top", anchor="w")
        # 新增按钮
        button_new = tk.Button(top_frame, text="新增", command=self.open_add_form)
        button_new.pack(side="left", anchor="w", padx=5, pady=5)
        # 导出按钮
        button_new = tk.Button(top_frame, text="全部导出", command=self.export_excel)
        button_new.pack(side="right", anchor="e", padx=5, pady=5)
        # 删除按钮
        button_new = tk.Button(top_frame, text="删除", command=self.delete_row)
        button_new.pack(side="right", anchor="e", padx=5, pady=5)

        def handle_date_selection(evt):
            selected_date = search_date.get_date()
            self.sel_date = selected_date
            # messagebox.showinfo("选中的日期", selected_date)
            self.refresh_table()

        right_frame = tk.Frame(root)
        right_frame.pack(side="right", anchor="n")
        # 查看全部数据
        button_new = tk.Button(right_frame, text="全部数据", command=self.refresh_all_data_table)
        button_new.pack(side="top", anchor="w", padx=5, pady=5, fill="x")
        # 日期筛选条件
        search_date = Calendar(right_frame, selectmode="day", date_pattern="yyyy年mm月dd日", locale="zh_CN")
        search_date.bind("<<CalendarSelected>>", handle_date_selection)
        search_date.pack(side="bottom", anchor="e", padx=10, pady=5)

        # 获取数据并创建表格
        self.create_table(root)

        # 自动选择当前日期
        handle_date_selection(None)

        # 主事件循环
        root.mainloop()
        # 关闭数据库连接
        self.dataStore.close()

    def refresh_all_data_table(self, is_all: bool = False):
        self.refresh_table(is_all=True)

    def refresh_table(self, is_all: bool = False):
        # 清空表格
        self.table.delete(*self.table.get_children())
        # 获取最新数据并插入表格
        if is_all:
            data = self.dataStore.fetch_all_data()
        else:
            data = self.dataStore.fetch_data(self.sel_date)
        for i, row in enumerate(data):
            row = GOODS.translate_to_name(row)
            self.table.insert(parent='', index='end', iid=i, values=row)

    def create_table(self, root):
        # 创建表格
        table_container = tk.Frame(root)
        table_container.pack(fill="both", expand=True, padx=6, pady=6)

        scrollbar = ttk.Scrollbar(table_container)
        table = ttk.Treeview(table_container, yscrollcommand=scrollbar.set, show='headings')
        scrollbar.config(command=table.yview)

        self.table = table
        table['columns'] = ('ID', 'Name', 'Type', 'Data Date', 'Make Type', 'Jiedai', 'Guige', 'Num', 'Price', 'Amount')

        # 设置列标题
        # table.heading('#0', text='Index')
        # table.column('#0', width=50)
        table.heading('ID', text='ID')
        table.column('ID', width=100)
        table.heading('Name', text='物品名称')
        table.column('Name', width=150)
        table.heading('Type', text='物品类型')
        table.column('Type', width=100)
        table.heading('Data Date', text='日期')
        table.column('Data Date', width=100)
        table.heading('Make Type', text='摘要')
        table.column('Make Type', width=150)
        table.heading('Jiedai', text='借贷')
        table.column('Jiedai', width=100)
        table.heading('Guige', text='规格')
        table.column('Guige', width=100)
        table.heading('Num', text='数量')
        table.column('Num', width=100)
        table.heading('Price', text='单价')
        table.column('Price', width=100)
        table.heading('Amount', text='金额')
        table.column('Amount', width=100)

        table.pack(side="left", fill="both", expand=True)
        scrollbar.pack(side="left", fill="y")

    def open_add_form(self):
        if self.is_open_add_form:
            return
        self.is_open_add_form = True

        def add_data():
            name = entry_name.get()
            type = entry_type.get()
            data_date = entry_data_date.get_date()
            # data_date = entry_data_date.get()
            make_type = entry_make_type.get()
            jiedai = entry_jiedai.get()
            guige = entry_guige.get()
            num = entry_num.get()
            price = entry_price.get()
            amount = entry_amount.get()

            # 将数据插入数据库
            goods_detail = (name, type, data_date, make_type, jiedai, guige, int(num), float(price), float(amount))
            goods_detail = GOODS.translate_to_code(goods_detail)
            is_success = self.dataStore.save_goods_detail(goods_detail)
            if is_success:
                messagebox.showinfo("提示", "数据添加成功!")
                self.refresh_table()
                # top.destroy()

        # 创建新增表单的窗口
        # top = tk.Frame(self.root)
        # top.pack(padx=20, pady=20, anchor="w")
        top = tk.Toplevel(self.root)

        def close_window():
            self.is_open_add_form = False
            top.destroy()
            return True

        top.protocol("WM_DELETE_WINDOW", close_window)
        # top.geometry("300x500")
        top.title("录入数据")

        row = 1
        pady = 3
        # tk.Label(top, text="录入数据：").grid(row=row, column=0, pady=8)
        # row += 1
        label_name = tk.Label(top, text="物品名称", anchor="e")
        label_name.grid(row=row, column=0, pady=pady)
        entry_name = tk.Entry(top)
        entry_name.grid(row=row, column=1, pady=pady)
        row += 1
        label_type = tk.Label(top, text="物品类型", anchor="e")
        label_type.grid(row=row, column=0, pady=pady)
        entry_type = tk.Entry(top)
        entry_type.grid(row=row, column=1, pady=pady)
        row += 1
        label_data_date = tk.Label(top, text="日期", anchor="e")
        label_data_date.grid(row=row, column=0, pady=pady)
        entry_data_date = Calendar(top, selectmode="day", date_pattern="yyyy年mm月dd日", locale="zh_CN")
        # entry_data_date = tk.Entry(top)
        entry_data_date.grid(row=row, column=1, pady=pady)
        row += 1
        label_make_type = tk.Label(top, text="摘要", anchor="e")
        label_make_type.grid(row=row, column=0, pady=pady)
        entry_make_type = tk.Entry(top)
        entry_make_type.grid(row=row, column=1, pady=pady)
        row += 1
        label_jiedai = tk.Label(top, text="借贷标记", anchor="e")
        label_jiedai.grid(row=row, column=0, pady=pady)

        entry_jiedai = tk.StringVar()
        entry_jiedai.set("1")
        panel1 = tk.Frame(top)
        radio_button1 = tk.Radiobutton(panel1, text="借", variable=entry_jiedai, value="1")
        radio_button1.grid(row=1, column=1, pady=pady)
        radio_button2 = tk.Radiobutton(panel1, text="贷", variable=entry_jiedai, value="2")
        radio_button2.grid(row=1, column=2, pady=pady)
        radio_button3 = tk.Radiobutton(panel1, text="结存", variable=entry_jiedai, value="3")
        radio_button3.grid(row=1, column=3, pady=pady)
        panel1.grid(row=row, column=1)
        # entry_jiedai = tk.Entry(top)
        # entry_jiedai.grid(row=row, column=1)

        row += 1
        label_guige = tk.Label(top, text="规格", anchor="e")
        label_guige.grid(row=row, column=0, pady=pady)
        entry_guige = tk.Entry(top)
        entry_guige.grid(row=row, column=1, pady=pady)

        def validate_number1(input):
            if input.isdigit():
                try:
                    price = Decimal(entry_price.get())
                    entry_amount.delete(0, tk.END)
                    entry_amount.insert(0, str(int(entry_num.get()) * price))
                except BaseException:
                    pass
            else:
                if input is None or input == '':
                    return False
                messagebox.showwarning("警告", "请输入整数数字！")
                entry_num.delete(0, tk.END)
                return False

        def validate_number2(input):
            try:
                price = Decimal(entry_price.get())
                if entry_num.get().isdigit():
                    entry_amount.delete(0, tk.END)
                    entry_amount.insert(0, str(int(entry_num.get()) * price))
                return True
            except BaseException:
                if input is None or input == '':
                    return False
                messagebox.showwarning("警告", "请输入数字！")
                entry_price.delete(0, tk.END)
                return False

        row += 1
        label_num = tk.Label(top, text="数量", anchor="e")
        label_num.grid(row=row, column=0, pady=pady)
        entry_num = tk.Entry(top, validate="focusout", validatecommand=(top.register(validate_number1), "%P"))
        entry_num.grid(row=row, column=1, pady=pady)
        row += 1
        label_price = tk.Label(top, text="单价", anchor="e")
        label_price.grid(row=row, column=0, pady=pady)
        entry_price = tk.Entry(top, validate="focusout", validatecommand=(top.register(validate_number2), "%P"))
        entry_price.grid(row=row, column=1, pady=pady)

        row += 1
        label_amount = tk.Label(top, text="金额", anchor="e")
        label_amount.grid(row=row, column=0, pady=pady)

        def validate_number3(input):
            try:
                Decimal(input)
                return True
            except BaseException:
                if input is None or input == '':
                    return False
                messagebox.showwarning("警告", "请输入数字！")
                entry_amount.delete(0, tk.END)
                return False

        entry_amount = tk.Entry(top, validate="focusout", validatecommand=(top.register(validate_number3), "%P"))
        entry_amount.grid(row=row, column=1, pady=pady)
        row += 1
        button_add = tk.Button(top, text="保存", command=add_data)
        button_add.grid(row=row, column=0, pady=pady)

    def delete_row(self):
        selection_items = self.table.selection()
        if len(selection_items) == 0:
            messagebox.showinfo("提示", "请选择要删除的行!")
        for item in selection_items:
            self.dataStore.del_good(self.table.item(item).get("values")[0])
            self.table.delete(item)

    def export_excel(self):
        goods_data = self.dataStore.fetch_all_data()
        DataExport.export_goods(goods_data)
        messagebox.showinfo("提示", "数据导出成功!")


if __name__ == '__main__':
    DataManage().run()
